from django.shortcuts import render, redirect, get_object_or_404
from django.http import JsonResponse
from django.conf import settings
from django.views import View
from rest_framework.views import APIView
from rest_framework.response import Response
import json
import os
import pymysql
import psycopg2
import sqlite3
from .forms import RemoteDatabaseForm, DataSourceForm
from django.urls import reverse_lazy, reverse
from django.views.generic import ListView, CreateView, UpdateView, DeleteView
from django.contrib import messages
from .models import DataSource, DataSourceConfig, Agent, SQLConfig
from .agent import MyAgent
from django.views.decorators.http import require_GET

class ConfigView(View):
    """配置页面视图"""
    def get(self, request):
        form = RemoteDatabaseForm()
        return render(request, 'config.html', {'form': form})

class TestConnectionView(View):
    """测试数据库连接"""
    def post(self, request, *args, **kwargs):
        """处理测试连接请求"""
        try:
            # 获取表单数据
            name = request.POST.get('name', '').strip()
            engine = request.POST.get('engine', '').strip()
            host = request.POST.get('host', '').strip()
            port = int(request.POST.get('port', 0))
            database = request.POST.get('database', '').strip()
            user = request.POST.get('user', '').strip()
            password = request.POST.get('password', '').strip()
            
            # 验证必填字段
            if not all([engine, host, port, database, user]):
                return JsonResponse({'status': 'error', 'message': '请填写所有必填字段'})
            
            # 测试连接并获取表数量
            conn, table_count = self.test_connection(engine, host, port, database, user, password)
            if conn:
                conn.close()
                
                # 保存连接状态和表数量到数据库
                try:
                    # 查找对应的数据源实例
                    pk = kwargs.get('pk')
                    if pk:
                        datasource = DataSource.objects.get(pk=pk)
                    else:
                        # 如果是新建数据源，根据名称查找最新的
                        datasource = DataSource.objects.filter(
                            name=name,
                            engine=engine,
                            host=host,
                            port=port,
                            database=database,
                            user=user
                        ).latest('created_at')
                    
                    datasource.connection_status = True
                    datasource.table_count = table_count
                    datasource.save()
                except DataSource.DoesNotExist:
                    pass  # 如果找不到数据源实例，不保存状态
                
                return JsonResponse({
                    'status': 'success',
                    'message': f'数据库连接成功！共发现 {table_count} 个表',
                    'table_count': table_count
                })
            else:
                # 更新连接状态为失败
                try:
                    pk = kwargs.get('pk')
                    if pk:
                        datasource = DataSource.objects.get(pk=pk)
                        datasource.connection_status = False
                        datasource.table_count = 0
                        datasource.save()
                except DataSource.DoesNotExist:
                    pass
                
                return JsonResponse({'status': 'error', 'message': '无法连接到数据库'})
                
        except ValueError as e:
            return JsonResponse({'status': 'error', 'message': '端口格式错误'})
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': f'测试连接时出错: {str(e)}'})
        
        # 如果所有步骤都成功完成
        return JsonResponse({'status': 'success', 'message': '测试连接完成'})

    def test_connection(self, engine, host, port, database, user, password):
        """测试数据库连接并返回表数量"""
        conn = None
        try:
            if engine == 'mysql':
                conn = pymysql.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    database=database,
                    charset='utf8mb4',
                    connect_timeout=10
                )
            elif engine == 'postgresql':
                conn = psycopg2.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    dbname=database,
                    connect_timeout=10
                )
            elif engine == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(host, port, service_name=database)
                conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
            elif engine == 'sqlite':
                conn = sqlite3.connect(database)
            else:
                return None, 0

            if conn:
                table_count = self.get_table_count(conn, engine)
                return conn, table_count
            return None, 0
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None, 0
            
    def get_table_count(self, conn, engine):
        """获取数据库中的表数量"""
        try:
            cursor = conn.cursor()
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                return len(cursor.fetchall())
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT COUNT(*) FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                return cursor.fetchone()[0]
            elif engine == 'oracle':
                cursor.execute("SELECT COUNT(*) FROM user_tables")
                return cursor.fetchone()[0]
            elif engine == 'sqlite':
                cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
                return cursor.fetchone()[0]
            else:
                return 0
        except Exception:
            return 0
        finally:
            cursor.close()

    def connect_database(self, config):
        """根据数据库类型连接数据库"""
        try:
            if config['engine'] == 'mysql':
                return pymysql.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    database=config['database'],
                    charset='utf8mb4'
                )
            elif config['engine'] == 'postgresql':
                return psycopg2.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    dbname=config['database']
                )
            elif config['engine'] == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(config['host'], config['port'], service_name=config['database'])
                return cx_Oracle.connect(user=config['user'], password=config['password'], dsn=dsn)
            elif config['engine'] == 'sqlite':
                import sqlite3
                return sqlite3.connect(config['database'])
            else:
                return None
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None

    def get_tables(self, conn, engine):
        """获取数据库中的表列表"""
        tables = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT table_name FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            
            # 为每个表获取字段信息
            for table in tables:
                table['fields'] = self.get_table_fields(conn, engine, table['name'])
                
            return tables
        finally:
            cursor.close()

    def get_table_fields(self, conn, engine, table_name):
        """获取表的字段信息"""
        fields = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, column_comment 
                    FROM information_schema.columns 
                    WHERE table_schema = DATABASE() AND table_name = %s
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, 
                           pg_catalog.col_description(c.oid, cols.ordinal_position) as column_comment
                    FROM information_schema.columns cols 
                    JOIN pg_catalog.pg_class c ON c.relname = cols.table_name 
                    WHERE cols.table_name = %s AND cols.table_schema = 'public'
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            return fields
        finally:
            cursor.close()


class TestConnectionView(View):
    """测试数据库连接"""
    def post(self, request, *args, **kwargs):
        """处理测试连接请求"""
        try:
            # 获取表单数据
            name = request.POST.get('name', '').strip()
            engine = request.POST.get('engine', '').strip()
            host = request.POST.get('host', '').strip()
            port = int(request.POST.get('port', 0))
            database = request.POST.get('database', '').strip()
            user = request.POST.get('user', '').strip()
            password = request.POST.get('password', '').strip()
            
            # 验证必填字段
            if not all([engine, host, port, database, user]):
                return JsonResponse({'status': 'error', 'message': '请填写所有必填字段'})
            
            # 测试连接并获取表数量
            conn, table_count = self.test_connection(engine, host, port, database, user, password)
            if conn:
                conn.close()
                
                # 保存连接状态和表数量到数据库
                try:
                    # 查找对应的数据源实例
                    pk = kwargs.get('pk')
                    if pk:
                        datasource = DataSource.objects.get(pk=pk)
                    else:
                        # 如果是新建数据源，根据名称查找最新的
                        datasource = DataSource.objects.filter(
                            name=name,
                            engine=engine,
                            host=host,
                            port=port,
                            database=database,
                            user=user
                        ).latest('created_at')
                    
                    datasource.connection_status = True
                    datasource.table_count = table_count
                    datasource.save()
                except DataSource.DoesNotExist:
                    pass  # 如果找不到数据源实例，不保存状态
                
                return JsonResponse({
                    'status': 'success',
                    'message': f'数据库连接成功！共发现 {table_count} 个表',
                    'table_count': table_count
                })
            else:
                # 更新连接状态为失败
                try:
                    pk = kwargs.get('pk')
                    if pk:
                        datasource = DataSource.objects.get(pk=pk)
                        datasource.connection_status = False
                        datasource.table_count = 0
                        datasource.save()
                except DataSource.DoesNotExist:
                    pass
                
                return JsonResponse({'status': 'error', 'message': '无法连接到数据库'})
                
        except ValueError as e:
            return JsonResponse({'status': 'error', 'message': '端口格式错误'})
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': f'测试连接时出错: {str(e)}'})
        
        # 如果所有步骤都成功完成
        return JsonResponse({'status': 'success', 'message': '测试连接完成'})

    def test_connection(self, engine, host, port, database, user, password):
        """测试数据库连接并返回表数量"""
        conn = None
        try:
            if engine == 'mysql':
                conn = pymysql.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    database=database,
                    charset='utf8mb4',
                    connect_timeout=10
                )
            elif engine == 'postgresql':
                conn = psycopg2.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    dbname=database,
                    connect_timeout=10
                )
            elif engine == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(host, port, service_name=database)
                conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
            elif engine == 'sqlite':
                conn = sqlite3.connect(database)
            else:
                return None, 0

            if conn:
                table_count = self.get_table_count(conn, engine)
                return conn, table_count
            return None, 0
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None, 0
            
    def get_table_count(self, conn, engine):
        """获取数据库中的表数量"""
        try:
            cursor = conn.cursor()
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                return len(cursor.fetchall())
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT COUNT(*) FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                return cursor.fetchone()[0]
            elif engine == 'oracle':
                cursor.execute("SELECT COUNT(*) FROM user_tables")
                return cursor.fetchone()[0]
            elif engine == 'sqlite':
                cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
                return cursor.fetchone()[0]
            else:
                return 0
        except Exception:
            return 0
        finally:
            cursor.close()

    def connect_database(self, config):
        """根据数据库类型连接数据库"""
        try:
            if config['engine'] == 'mysql':
                return pymysql.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    database=config['database'],
                    charset='utf8mb4'
                )
            elif config['engine'] == 'postgresql':
                return psycopg2.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    dbname=config['database']
                )
            elif config['engine'] == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(config['host'], config['port'], service_name=config['database'])
                return cx_Oracle.connect(user=config['user'], password=config['password'], dsn=dsn)
            elif config['engine'] == 'sqlite':
                import sqlite3
                return sqlite3.connect(config['database'])
            else:
                return None
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None

    def get_tables(self, conn, engine):
        """获取数据库中的表列表"""
        tables = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT table_name FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            
            # 为每个表获取字段信息
            for table in tables:
                table['fields'] = self.get_table_fields(conn, engine, table['name'])
                
            return tables
        finally:
            cursor.close()

    def get_table_fields(self, conn, engine, table_name):
        """获取表的字段信息"""
        fields = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, column_comment 
                    FROM information_schema.columns 
                    WHERE table_schema = DATABASE() AND table_name = %s
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, 
                           pg_catalog.col_description(c.oid, cols.ordinal_position) as column_comment
                    FROM information_schema.columns cols 
                    JOIN pg_catalog.pg_class c ON c.relname = cols.table_name 
                    WHERE cols.table_name = %s AND cols.table_schema = 'public'
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            return fields
        finally:
            cursor.close()


def agent_management(request):
    """智能体管理页面视图"""
    agents = Agent.objects.select_related('datasource').all()
    return render(request, 'agent_management.html', {'agents': agents})

def agent_form(request, pk=None):
    """统一的智能体表单视图（新增和编辑共用）"""
    if pk:
        try:
            agent = Agent.objects.get(pk=pk)
        except Agent.DoesNotExist:
            error_msg = '智能体不存在'
            messages.error(request, error_msg)
            if request.content_type == 'application/json':
                return JsonResponse({'success': False, 'message': error_msg})
            else:
                return redirect('agent_management')
    else:
        agent = None

    print(agent)
    if request.method == 'POST':
        # 处理表单提交
        if request.content_type == 'application/json':
            # 解析JSON请求体
            import json
            try:
                data = json.loads(request.body)
            except json.JSONDecodeError:
                messages.error(request, '无效的JSON数据')
                return JsonResponse({'success': False, 'message': '无效的JSON数据'})
            
            agent_name = data.get('agentName', '').strip()
            agent_description = data.get('agentDescription', '').strip()
            datasource_id = data.get('datasourceId', '').strip()
            execution_type = data.get('executionType', 'manual')
            destination_agent = data.get('destinationAgent', '').strip()
            
            
            # 调度相关参数
            interval_value = data.get('intervalValue', '1')
            interval_unit = data.get('intervalUnit', 'days')
            cron_expression = data.get('cronExpression', '')
            execution_start_time = data.get('executionStartTime', '')
            
            # 表和字段选择
            selected_tables = data.get('selected_tables', {})
        else:
            # 处理表单编码请求
            agent_name = request.POST.get('agentName', '').strip()
            agent_description = request.POST.get('agentDescription', '').strip()
            datasource_id = request.POST.get('datasourceId', '').strip()
            execution_type = request.POST.get('executionType', 'manual')
            destination_agent = request.POST.get('destinationAgent', '').strip()
            
            # 调度相关参数
            interval_value = request.POST.get('intervalValue', '1')
            interval_unit = request.POST.get('intervalUnit', 'days')
            cron_expression = request.POST.get('cronExpression', '')
            execution_start_time = request.POST.get('executionStartTime', '')
            
            # 表和字段选择
            selected_tables = {}
            for key, value in request.POST.items():
                if key.startswith('table_'):
                    table_name = key.replace('table_', '')
                    if value == 'on':
                        fields_key = f'fields_{table_name}'
                        fields = request.POST.get(fields_key, '')
                        if fields:
                            selected_tables[table_name] = fields.split(',')
        
        # schedule_type参数已弃用，使用execution_type代替
        schedule_type = execution_type
        
        # 验证必填字段
        if not all([agent_name, datasource_id, destination_agent]):
            error_msg = '请填写所有必填字段'
            messages.error(request, error_msg)
            datasources = DataSource.objects.all()
            if request.content_type == 'application/json':
                return JsonResponse({'success': False, 'message': error_msg})
            else:
                return render(request, 'agent_form_unified.html', {
                    'agent': agent,
                    'datasources': datasources,
                    'form_data': request.POST
                })
        
        # 获取数据源
        try:
            datasource = DataSource.objects.get(pk=datasource_id)
        except DataSource.DoesNotExist:
            error_msg = '数据源不存在'
            messages.error(request, error_msg)
            datasources = DataSource.objects.all()
            if request.content_type == 'application/json':
                return JsonResponse({'success': False, 'message': error_msg})
            else:
                return render(request, 'agent_form_unified.html', {
                    'agent': agent,
                    'datasources': datasources,
                    'form_data': request.POST
                })
        
        # 设置执行参数
        execution_interval = None
        cron_expr = None
        
        if execution_type == 'interval':
            try:
                execution_interval = int(interval_value)
                if interval_unit == 'hours':
                    execution_interval *= 60
                elif interval_unit == 'days':
                    execution_interval *= 60 * 24
            except ValueError:
                execution_interval = 60
        elif execution_type == 'cron':
            cron_expr = cron_expression
        
        # 保存智能体配置
        try:
            if agent:  # 编辑模式
                agent.name = agent_name
                agent.description = agent_description
                agent.datasource = datasource
                agent.execution_type = execution_type
                agent.execution_interval = execution_interval
                agent.cron_expression = cron_expr
                agent.target_agent = destination_agent
                agent.selected_tables = selected_tables
                agent.execution_start_time = execution_start_time
                agent.config_data = {
                    'interval_value': interval_value,
                    'interval_unit': interval_unit,
                    'cron_expression': cron_expression
                }
                agent.save()
                messages.success(request, f'智能体 "{agent_name}" 更新成功')
            else:  # 新增模式
                agent = Agent.objects.create(
                    name=agent_name,
                    description=agent_description,
                    datasource=datasource,
                    execution_type=execution_type,
                    execution_interval=execution_interval,
                    cron_expression=cron_expr,
                    target_agent=destination_agent,
                    selected_tables=selected_tables,
                    config_data={
                        'interval_value': interval_value,
                        'interval_unit': interval_unit,
                        'cron_expression': cron_expression
                    }
                )
                messages.success(request, f'智能体 "{agent_name}" 创建成功')
            
            if request.content_type == 'application/json':
                return JsonResponse({'success': True, 'message': f'智能体 "{agent_name}" {"更新" if agent else "创建"}成功', 'redirect_url': reverse('agent_management')})
            else:
                return redirect('agent_management')
            
        except Exception as e:
            error_msg = f'保存失败: {str(e)}'
            messages.error(request, error_msg)
            datasources = DataSource.objects.all()
            if request.content_type == 'application/json':
                return JsonResponse({'success': False, 'message': error_msg})
            else:
                return render(request, 'agent_form_unified.html', {
                    'agent': agent,
                    'datasources': datasources,
                    'form_data': request.POST
                })
    
    else:
        # 显示表单
        datasources = DataSource.objects.all()
        
        # 为编辑模式准备选中的表和字段数据
        selected_tables_json = {}
        if agent and agent.selected_tables:
            selected_tables_json = agent.selected_tables
            
        return render(request, 'agent_form_unified.html', {
            'agent': agent,
            'datasources': datasources,
            'selected_tables_json': selected_tables_json
        })

def agent_create(request):
    """重定向到统一的表单视图"""
    return redirect('agent_form')

def agent_config(request, pk):
    """重定向到统一的表单视图"""
    return redirect('agent_form', pk=pk)
          
    def test_connection(self, engine, host, port, database, user, password):
        """测试数据库连接并返回表数量"""
        conn = None
        try:
            if engine == 'mysql':
                conn = pymysql.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    database=database,
                    charset='utf8mb4',
                    connect_timeout=10
                )
            elif engine == 'postgresql':
                conn = psycopg2.connect(
                    host=host,
                    port=port,
                    user=user,
                    password=password,
                    dbname=database,
                    connect_timeout=10
                )
            elif engine == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(host, port, service_name=database)
                conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
            elif engine == 'sqlite':
                conn = sqlite3.connect(database)
            else:
                return None, 0

            if conn:
                table_count = self.get_table_count(conn, engine)
                return conn, table_count
            return None, 0
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None, 0
            
    def get_table_count(self, conn, engine):
        """获取数据库中的表数量"""
        try:
            cursor = conn.cursor()
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                return len(cursor.fetchall())
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT COUNT(*) FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                return cursor.fetchone()[0]
            elif engine == 'oracle':
                cursor.execute("SELECT COUNT(*) FROM user_tables")
                return cursor.fetchone()[0]
            elif engine == 'sqlite':
                cursor.execute("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
                return cursor.fetchone()[0]
            else:
                return 0
        except Exception:
            return 0
        finally:
            cursor.close()

    def connect_database(self, config):
        """根据数据库类型连接数据库"""
        try:
            if config['engine'] == 'mysql':
                return pymysql.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    database=config['database'],
                    charset='utf8mb4'
                )
            elif config['engine'] == 'postgresql':
                return psycopg2.connect(
                    host=config['host'],
                    port=config['port'],
                    user=config['user'],
                    password=config['password'],
                    dbname=config['database']
                )
            elif config['engine'] == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(config['host'], config['port'], service_name=config['database'])
                return cx_Oracle.connect(user=config['user'], password=config['password'], dsn=dsn)
            elif config['engine'] == 'sqlite':
                import sqlite3
                return sqlite3.connect(config['database'])
            else:
                return None
        except Exception as e:
            print(f"数据库连接错误: {str(e)}")
            return None

    def get_tables(self, conn, engine):
        """获取数据库中的表列表"""
        tables = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("SHOW TABLES")
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT table_name FROM information_schema.tables 
                    WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
                """)
                tables = [{'name': table[0]} for table in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            
            # 为每个表获取字段信息
            for table in tables:
                table['fields'] = self.get_table_fields(conn, engine, table['name'])
                
            return tables
        finally:
            cursor.close()

    def get_table_fields(self, conn, engine, table_name):
        """获取表的字段信息"""
        fields = []
        cursor = conn.cursor()
        
        try:
            if engine == 'mysql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, column_comment 
                    FROM information_schema.columns 
                    WHERE table_schema = DATABASE() AND table_name = %s
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            elif engine == 'postgresql':
                cursor.execute("""
                    SELECT column_name, data_type, is_nullable, 
                           pg_catalog.col_description(c.oid, cols.ordinal_position) as column_comment
                    FROM information_schema.columns cols 
                    JOIN pg_catalog.pg_class c ON c.relname = cols.table_name 
                    WHERE cols.table_name = %s AND cols.table_schema = 'public'
                """, [table_name])
                fields = [{
                    'name': row[0],
                    'type': row[1],
                    'nullable': row[2] == 'YES',
                    'description': row[3] or ''
                } for row in cursor.fetchall()]
            # 可以添加其他数据库类型的支持
            
            return fields
        finally:
            cursor.close()

@require_GET
def get_table_fields(request, pk):
    try:
        datasource = DataSource.objects.get(pk=pk)
        conn_params = {
            'engine': datasource.engine,
            'name': datasource.database,
            'user': datasource.user,
            'password': datasource.password,
            'host': datasource.host,
            'port': datasource.port or None
        }
    
        tables = []
        conn = None
        cursor = None
    
        try:
            if conn_params['engine'] == 'mysql':
                conn = pymysql.connect(
                    host=conn_params['host'],
                    user=conn_params['user'],
                    password=conn_params['password'],
                    database=conn_params['name'],
                    port=conn_params['port'] or 3306
                )
                cursor = conn.cursor()
                cursor.execute("SHOW TABLES")
                table_names = [row[0] for row in cursor.fetchall()]
                for table in table_names:
                    cursor.execute("SELECT column_name, data_type, is_nullable, column_comment FROM information_schema.columns WHERE table_schema = %s AND table_name = %s", [conn_params['name'], table])
                    fields = [{
                        'name': row[0],
                        'type': row[1],
                        'nullable': row[2] == 'YES',
                        'description': row[3] or ''
                    } for row in cursor.fetchall()]
                    tables.append({'name': table, 'field_count': len(fields), 'fields': fields})
            elif conn_params['engine'] == 'postgresql':
                conn = psycopg2.connect(
                    host=conn_params['host'],
                    user=conn_params['user'],
                    password=conn_params['password'],
                    dbname=conn_params['name'],
                    port=conn_params['port'] or 5432
                )
                cursor = conn.cursor()
                cursor.execute("SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")
                table_names = [row[0] for row in cursor.fetchall()]
                for table in table_names:
                    cursor.execute("SELECT column_name, data_type, is_nullable, pg_catalog.col_description(c.oid, cols.ordinal_position) as column_comment FROM information_schema.columns cols JOIN pg_catalog.pg_class c ON c.relname = cols.table_name WHERE cols.table_name = %s AND cols.table_schema = 'public'", [table])
                    fields = [{
                        'name': row[0],
                        'type': row[1],
                        'nullable': row[2] == 'YES',
                        'description': row[3] or ''
                    } for row in cursor.fetchall()]
                    tables.append({'name': table, 'field_count': len(fields), 'fields': fields})
            elif conn_params['engine'] == 'oracle':
                import cx_Oracle
                dsn = cx_Oracle.makedsn(conn_params['host'], conn_params['port'] or 1521, service_name=conn_params['name'])
                conn = cx_Oracle.connect(user=conn_params['user'], password=conn_params['password'], dsn=dsn)
                cursor = conn.cursor()
                cursor.execute("SELECT table_name FROM user_tables")
                table_names = [row[0] for row in cursor.fetchall()]
                for table in table_names:
                    cursor.execute("SELECT column_name, data_type, nullable, comments FROM user_tab_columns LEFT JOIN user_col_comments ON user_tab_columns.table_name = user_col_comments.table_name AND user_tab_columns.column_name = user_col_comments.column_name WHERE table_name = :1", [table.upper()])
                    fields = [{
                        'name': row[0],
                        'type': row[1],
                        'nullable': row[2] == 'Y',
                        'description': row[3] or ''
                    } for row in cursor.fetchall()]
                    tables.append({'name': table, 'field_count': len(fields), 'fields': fields})
            elif conn_params['engine'] == 'sqlite':
                conn = sqlite3.connect(conn_params['name'])
                cursor = conn.cursor()
                cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'")
                table_names = [row[0] for row in cursor.fetchall()]
                for table in table_names:
                    cursor.execute("PRAGMA table_info({})".format(table))
                    fields = []
                    for field in cursor.fetchall():
                        cursor.execute("SELECT sql FROM sqlite_master WHERE name = ? AND type = 'table'", [table])
                        schema = cursor.fetchone()[0] if cursor.rowcount > 0 else ''
                        comment = extract_sqlite_column_comment(schema, field[1])
                        fields.append({
                            'name': field[1],
                            'type': field[2],
                            'nullable': field[3] == 0,
                            'description': comment
                        })
                    tables.append({'name': table, 'field_count': len(fields), 'fields': fields})
            return JsonResponse({'tables': tables})
        finally:
            if cursor: cursor.close()
            if conn: conn.close()
    except DataSource.DoesNotExist:
        return JsonResponse({'error': '数据源不存在'}, status=404)
    except Exception as e:
        return JsonResponse({'error': str(e)}, status=500)

# SQLite 辅助函数
def extract_sqlite_column_comment(schema, column_name):
    import re
    schema = re.sub(r'\/\*.*?\*\/', '', schema, flags=re.DOTALL)
    lines = [re.sub(r'--.*$', '', line).strip() for line in schema.split('\n')]
    for line in lines:
        if column_name in line and (' ' + column_name + ' ' in ' ' + line + ' ' or line.startswith(column_name + ' ') or line.endswith(' ' + column_name)):
            next_line_idx = lines.index(line) + 1
            if next_line_idx < len(lines):
                next_line = lines[next_line_idx].strip()
                if next_line.startswith('--'):
                    return next_line[2:].strip()
    return ''

from django.http import HttpResponse, FileResponse
import io

class SaveConfigView(View):
    """保存配置到JSON文件并提供直接下载"""
    def post(self, request):
        try:
            data = json.loads(request.body)
            config = data['config']
            selected_tables = data['selected_tables']
            
            # 构建完整配置
            full_config = {
                'name': config['name'],
                'engine': config['engine'],
                'connection': {
                    'host': config['host'],
                    'port': config['port'],
                    'database': config['database'],
                    'user': config['user'],
                    'password': config['password']
                },
                'selected_tables': selected_tables
            }
            
            # 生成JSON内容
            json_content = json.dumps(full_config, ensure_ascii=False, indent=2)
            
            # 创建文件下载响应
            filename = f"{config['name']}_config.json"
            response = HttpResponse(json_content, content_type='application/json')
            response['Content-Disposition'] = f'attachment; filename="{filename}"'
            
            return response
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)})

class DataSourceListView(ListView):
    model = DataSource
    template_name = 'datasource_list.html'
    context_object_name = 'datasources'
    
    def get_queryset(self):
        queryset = super().get_queryset()
        for datasource in queryset:
            # 设置默认表数量为0
            datasource._table_count = 0
        return queryset

class DataSourceCreateView(CreateView):
    model = DataSource
    form_class = DataSourceForm
    template_name = 'datasource_form.html'
    success_url = reverse_lazy('datasource_list')
    
    def form_valid(self, form):
        # 保存前测试连接并更新状态
        instance = form.save(commit=False)
        
        # 测试连接
        test_view = TestConnectionView()
        conn, table_count = test_view.test_connection(
            instance.engine,
            instance.host,
            instance.port,
            instance.database,
            instance.user,
            instance.password
        )
        
        if conn:
            conn.close()
            instance.connection_status = True
            instance.table_count = table_count
            messages.success(self.request, f'数据源创建成功！连接正常，共发现 {table_count} 个表')
        else:
            instance.connection_status = False
            instance.table_count = 0
            messages.warning(self.request, '数据源创建成功！但数据库连接失败，请检查配置')
        
        instance.save()
        return super().form_valid(form)

class DataSourceUpdateView(UpdateView):
    model = DataSource
    form_class = DataSourceForm
    template_name = 'datasource_form.html'
    success_url = reverse_lazy('datasource_list')
    
    def form_valid(self, form):
        # 保存前测试连接并更新状态
        instance = form.save(commit=False)
        
        # 测试连接
        test_view = TestConnectionView()
        conn, table_count = test_view.test_connection(
            instance.engine,
            instance.host,
            instance.port,
            instance.database,
            instance.user,
            instance.password
        )
        
        if conn:
            conn.close()
            instance.connection_status = True
            instance.table_count = table_count
            messages.success(self.request, f'数据源更新成功！连接正常，共发现 {table_count} 个表')
        else:
            instance.connection_status = False
            instance.table_count = 0
            messages.warning(self.request, '数据源更新成功！但数据库连接失败，请检查配置')
        
        instance.save()
        return super().form_valid(form)

class DataSourceDeleteView(DeleteView):
    model = DataSource
    success_url = reverse_lazy('datasource_list')
    
    def delete(self, request, *args, **kwargs):
        messages.success(request, '数据源删除成功！')
        return super().delete(request, *args, **kwargs)

def datasource_config(request, pk):
    datasource = get_object_or_404(DataSource, pk=pk)
    
    if request.method == 'POST':
        try:
            data = json.loads(request.body)
            selected_tables = data.get('selected_tables', [])
            
            # 保存或更新配置
            config, created = DataSourceConfig.objects.get_or_create(
                datasource=datasource,
                defaults={'selected_tables': selected_tables}
            )
            if not created:
                config.selected_tables = selected_tables
                config.save()
            
            return JsonResponse({
                'status': 'success',
                'message': '配置保存成功'
            })
        except Exception as e:
            return JsonResponse({
                'status': 'error',
                'message': str(e)
            }, status=400)
    
    # GET请求 - 加载已保存的配置
    datasource = get_object_or_404(DataSource, pk=pk)
    
    # 获取已保存的配置
    try:
        config = DataSourceConfig.objects.get(datasource=datasource)
        saved_config = {
            'tables': config.selected_tables
        }
    except DataSourceConfig.DoesNotExist:
        saved_config = {'tables': []}
    
    return render(request, 'datasource_config.html', {
        'datasource': datasource,
        'saved_config': json.dumps(saved_config),
    })


# 获取数据源配置json

def get_datasource_config(request, pk):
    datasource = get_object_or_404(DataSource, pk=pk)
    
    # 获取已保存的配置
    try:
        config = DataSourceConfig.objects.get(datasource=datasource)
        saved_config = {
            'selected_tables': config.selected_tables
        }
    except DataSourceConfig.DoesNotExist:
        saved_config = {'selected_tables': []}
    
    return JsonResponse(saved_config)

# SQL配置API视图

def get_sql_configs(request, pk):
    """获取特定数据源的所有SQL配置"""
    datasource = get_object_or_404(DataSource, pk=pk)
    sql_configs = SQLConfig.objects.filter(datasource=datasource)
    
    result = {
        'sql_configs': [{
            'id': config.id,
            'name': config.name,
            'description': config.description,
            'config_type': config.config_type,
            'sql': config.sql,
            'params': config.params,
            'created_at': config.created_at.isoformat()
        } for config in sql_configs]
    }
    
    return JsonResponse(result)

def get_sql_config(request, pk):
    """获取单个SQL配置详情"""
    sql_config = get_object_or_404(SQLConfig, pk=pk)
    
    result = {
        'id': sql_config.id,
        'name': sql_config.name,
        'description': sql_config.description,
        'config_type': sql_config.config_type,
        'sql': sql_config.sql,
        'params': sql_config.params,
        'datasource_id': sql_config.datasource.id,
        'created_at': sql_config.created_at.isoformat()
    }
    
    return JsonResponse(result)


def sql_config_view(request, pk):
    """获取并返回SQL配置的详细信息"""
    config = get_object_or_404(SQLConfig, pk=pk)
    data = {
        'name': config.name,
        'description': config.description,
        'sql': config.sql,
        'params': config.params,
    }
    return JsonResponse(data)


def create_sql_config(request):
    """创建新的SQL配置"""
    if request.method != 'POST':
        return JsonResponse({'status': 'error', 'message': '只支持POST请求'}, status=405)
    
    try:
        data = json.loads(request.body)
        print(data)
        name = data.get('name')
        description = data.get('description', '')
        config_type = data.get('config_type', 'custom')
        sql = data.get('sql')
        params = data.get('params', '')
        datasource_id = data.get('datasource_id')
        
        if not name or not datasource_id:
            return JsonResponse({'status': 'error', 'message': '名称和数据源ID不能为空'}, status=400)
        
        if config_type == 'custom' and not sql:
            return JsonResponse({'status': 'error', 'message': '自定义SQL类型必须提供SQL语句'}, status=400)
        
        datasource = get_object_or_404(DataSource, pk=datasource_id)
        
        sql_config = SQLConfig.objects.create(
            name=name,
            description=description,
            config_type=config_type,
            sql=sql,
            params=params,
            datasource=datasource
        )
        
        return JsonResponse({
            'status': 'success',
            'message': 'SQL配置创建成功',
            'id': sql_config.id
        }, status=201)
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

def update_sql_config(request, pk):
    """更新SQL配置"""
    if request.method != 'PUT':
        return JsonResponse({'status': 'error', 'message': '只支持PUT请求'}, status=405)
    
    try:
        sql_config = get_object_or_404(SQLConfig, pk=pk)
        data = json.loads(request.body)
        
        name = data.get('name')
        description = data.get('description', '')
        config_type = data.get('config_type', 'custom')
        sql = data.get('sql')
        params = data.get('params', '')
        
        if not name:
            return JsonResponse({'status': 'error', 'message': '名称不能为空'}, status=400)
        
        if config_type == 'custom' and not sql:
            return JsonResponse({'status': 'error', 'message': '自定义SQL类型必须提供SQL语句'}, status=400)
        
        sql_config.name = name
        sql_config.description = description
        sql_config.config_type = config_type
        sql_config.sql = sql
        sql_config.params = params
        sql_config.save()
        
        return JsonResponse({
            'status': 'success',
            'message': 'SQL配置更新成功'
        })
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

def delete_sql_config(request, pk):
    """删除SQL配置"""
    if request.method != 'DELETE':
        return JsonResponse({'status': 'error', 'message': '只支持DELETE请求'}, status=405)
    
    try:
        sql_config = get_object_or_404(SQLConfig, pk=pk)
        sql_config.delete()
        
        return JsonResponse({
            'status': 'success',
            'message': 'SQL配置删除成功'
        })
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

def get_table_fields_for_sql_config(request, pk, table_name):
    """获取特定表的字段信息，用于SQL配置"""
    datasource = get_object_or_404(DataSource, pk=pk)
    
    try:
        # 创建数据库连接
        test_view = TestConnectionView()
        conn, _ = test_view.test_connection(
            datasource.engine,
            datasource.host,
            datasource.port,
            datasource.database,
            datasource.user,
            datasource.password
        )
        
        if not conn:
            return JsonResponse({'status': 'error', 'message': '无法连接到数据库'}, status=400)
        
        cursor = conn.cursor()
        fields = []
        
        if datasource.engine == 'mysql':
            cursor.execute(f"DESCRIBE {table_name}")
            for field in cursor.fetchall():
                fields.append({
                    'name': field[0],
                    'type': field[1],
                    'nullable': field[2] == 'YES'
                })
        elif datasource.engine == 'postgresql':
            cursor.execute(f"SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = %s", [table_name])
            for field in cursor.fetchall():
                fields.append({
                    'name': field[0],
                    'type': field[1],
                    'nullable': field[2] == 'YES'
                })
        elif datasource.engine == 'sqlite':
            cursor.execute(f"PRAGMA table_info({table_name})")
            for field in cursor.fetchall():
                fields.append({
                    'name': field[1],
                    'type': field[2],
                    'nullable': field[3] == 0
                })
        else:
            # 其他数据库类型的处理
            return JsonResponse({'status': 'error', 'message': f'不支持的数据库类型: {datasource.engine}'}, status=400)
        
        conn.close()
        
        return JsonResponse({'fields': fields})
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=400)


class AgentInstructionView(APIView):
    # permission_classes = [IsAuthenticated] # 保护你的API

    def post(self, request, *args, **kwargs):
        user_instruction = request.data.get("instruction")
        if not user_instruction:
            return Response({"error": "缺少 'instruction' 字段。"}, status=400)
            
        # 实例化并运行智能体
        agent = MyAgent()
        final_result = agent.run(user_instruction)
        
        return Response({"answer": final_result}) 